"""
Case Type   : ROLE
Case Name   : 创建角色，添加option选项，测试角色是否具有createdb权限和login权限
Create At   : 2022-11-16
Owner       : liu-tong-8848
Description :
    1、创建role，赋予createdb权限
    2、切换至r_role_0011_1角色，创建数据库
    3、创建role，先赋予login权限
    4、给r_role_0011_2角色赋予createdb权限
    5、查询r_role_0011_2是否拥有创建数据库的权限
    6、切换至r_role_0011_2角色，创建数据库
    7、清理环境;expect:成功
Expect      :
    1、成功
    2、合理报错
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
History     :
"""

import os
import unittest

from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Purge(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.r_name1 = 'r_role_0011_1'
        self.r_name2 = 'r_role_0011_2'
        self.d_name = 'd_role_0011'

    def test_main(self):
        step1_txt = '----step1: 创建角色1，expect: 成功----'
        self.log.info(step1_txt)
        sql_cmd1 = f"drop role if exists {self.r_name1};" \
                   f"create role {self.r_name1} createdb " \
                   f"password '{macro.COMMON_PASSWD}';"
        msg1 = self.pri_sh.execut_db_sql(sql_cmd1)
        self.log.info(msg1)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, msg1,
                      '执行失败:' + step1_txt)

        step2_txt = '----step2: 角色1连接数据库创建数据库，expect: 合理报错----'
        self.log.info(step2_txt)
        sql_cmd2 = f"drop database if exists {self.d_name};" \
                   f"create database {self.d_name};"
        excute_cmd2 = f'-U {self.r_name1} -W "{macro.COMMON_PASSWD}" '
        msg2 = self.pri_sh.execut_db_sql(sql_cmd2, excute_cmd2)
        self.log.info(msg2)
        self.assertIn(f'role "{self.r_name1}" is not permitted to login', msg2,
                      '执行失败:' + step2_txt)

        step3_txt = '----step3: 创建角色2，expect: 成功----'
        self.log.info(step3_txt)
        sql_cmd3 = f"drop role if exists {self.r_name2};" \
                   f"create role {self.r_name2} login " \
                   f"password '{macro.COMMON_PASSWD}';"
        msg3 = self.pri_sh.execut_db_sql(sql_cmd3)
        self.log.info(msg3)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, msg3,
                      '执行失败:' + step3_txt)

        step4_txt = '----step4: 给r_role_0011_2角色赋予createdb权限，expect: 成功----'
        self.log.info(step4_txt)
        sql_cmd4 = f"alter role {self.r_name2} createdb;"
        msg4 = self.pri_sh.execut_db_sql(sql_cmd4)
        self.log.info(msg4)
        self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, msg4,
                      '执行失败:' + step4_txt)

        step5_txt = '----step5:查询r_role_0011_2是否拥有创建数据库的权限，expect: 成功----'
        self.log.info(step5_txt)
        sql_cmd5 = f"select rolname,rolcreatedb from pg_authid " \
                   f"where rolname = '{self.r_name2}'"
        msg5 = self.pri_sh.execut_db_sql(sql_cmd5)
        self.log.info(msg5)
        self.assertIn(f"{self.r_name2} | t", msg5, '执行失败:' + step5_txt)

        step6_txt = '----step6: 切换至r_role_0011_2角色，创建数据库，expect: 成功----'
        self.log.info(step6_txt)
        sql_cmd6 = f"drop database if exists {self.d_name};" \
                   f"create database {self.d_name};"
        excute_cmd6 = f'-U {self.r_name2} -W "{macro.COMMON_PASSWD}" '
        msg6 = self.pri_sh.execut_db_sql(sql_cmd6, excute_cmd6)
        self.log.info(msg6)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg6,
                      '执行失败:' + step6_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step7_txt = '----step7: 清理环境----'
        self.log.info(step7_txt)
        drop_sql = f'drop database {self.d_name};' \
                   f'drop role if exists {self.r_name1}, {self.r_name2};'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      '执行失败:' + step7_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, drop_result,
                      '执行失败:' + step7_txt)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
